package negocio.daos;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import negocio.entidades.Disciplina;
import negocio.entidades.Matricula;
import negocio.entidades.Usuario;

public class MatriculaDAO {
	private EntityManager em;

	public MatriculaDAO(EntityManager em) {
		this.em = em;
	}

	public Matricula salvar(Matricula m) throws Exception {
		if (m.getId() > 0) {
			if (consultarPorId(m.getId()) != null) {
				em.merge(m);
			}
		} else {
			em.persist(m);
		}
		return m;
	}

	public void excluir(long id) throws Exception {
		Matricula m = consultarPorId(id);
		if (m != null) {
			em.remove(m);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Matricula> listar2(String matriculaAluno, String nomeAluno) throws Exception{
		Query q = em.createQuery(
				"select m from Matricula m left join m.aluno where m.aluno.nome like lower('"
						+ nomeAluno + "%') and m.aluno.matricula like lower('"
						+ matriculaAluno
						+ "%') group by m.aluno.id", Matricula.class);
		return q.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public List<Matricula> listar(String matriculaAluno, String nomeAluno) throws Exception{
		Query q = em.createQuery(
				"select distinct m from Matricula m left join m.aluno where m.aluno.nome like lower('"
						+ nomeAluno + "%') and m.aluno.matricula like lower('"
						+ matriculaAluno
						+ "%') group by m.aluno.id", Matricula.class);
		return q.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<Usuario> listarAlunos(String matriculaAluno, String nomeAluno) throws Exception{
		Query q = em.createQuery(
				"select distinct u from Usuario u left join fetch u.matriculas where u.perfil = 'ALUNO' and u.nome like lower('"
						+ nomeAluno + "%') and u.matricula like lower('"
						+ matriculaAluno
						+ "%')", Usuario.class);
		return q.getResultList();
	}
	
	public  Usuario findAlunoByMatricula(String matriculaAluno) throws Exception {
		Query q = em.createQuery(
				"select u from Usuario u left join u.matriculas where u.perfil = 'ALUNO' and u.matricula like lower('"
						+ matriculaAluno + "%')", Usuario.class);
		return (Usuario) q.getSingleResult();
	}
	
	@SuppressWarnings("unchecked")
	public List<Disciplina> listarDisciplinas() {
		Query q = em.createQuery(
				"select d from Disciplina d order by d.nome, d.dtInicio desc", Disciplina.class);
		return q.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public List<Disciplina> listarDisciplinas(String nomeDisciplina) throws Exception {
		Date d = new Date();
		Query q = em.createQuery(
				"select d from Disciplina d where d.dtFim <= '"+d+"' nome like lower('"
						+ nomeDisciplina + "%')", Disciplina.class);
		return q.getResultList();
	}

	public Matricula consultarPorId(long id) {
		Matricula m = (Matricula) em.createQuery(
				"select m from Matricula m where m.id =" + id)
				.getSingleResult();
		return m;
	}

	public Matricula findByAlunoDisciplina(Usuario aluno, Disciplina disciplina) throws Exception {
		Query q = em.createQuery(
				"select m from Matricula m where m.aluno.id = "
						+ aluno.getId()+" and m.disciplina.id ="
						+ disciplina.getId(), Matricula.class);
		return (Matricula) q.getSingleResult();
	}

	public int findNumMatriculadosDisciplina(Disciplina disciplina) throws Exception {
		Query q = em.createQuery(
				"select count(m.id) from Matricula m where m.disciplina.id = "
						+ disciplina.getId() +" and m.situacao = '"+"CURSANDO"+"'");
		return ((Number) q.getSingleResult()).intValue();
		
	}
}
